﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace U01_boke
{
    public class SqlHelper
    {
        #region 配置
        /// <summary>
        /// 获取数据库连接字符串基本信息
        /// </summary>
        private static ConnectionStringSettings connectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["SqlConn"];
            }
        }

        /// <summary>
        /// 获取一个值，判断请求是否来自本地计算机
        /// </summary>
        private static bool IsLocal
        {
            get
            {
                return Convert.ToBoolean(AppSettingsManage.GetValue(AppSetting.IsLocal));
            }
        }

        /// <summary>
        /// 创建和管理SqlConnection访问数据的字符串内容
        /// </summary>
        private static SqlConnectionStringBuilder Stringbuilder
        {
            get
            {
                if (null == connectionString)
                    throw new ConfigurationErrorsException("数据库配置:SqlConn不存在");
                SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connectionString.ConnectionString);
                if (IsLocal)
                {
                    builder.UserID = "sa";
                    builder.Password = "520@wujinmei";
                    builder.IntegratedSecurity = false;
                }
                return builder;
            }
        }
        #endregion

        #region SqlDataReader
        /// <summary>
        /// 返回SqlDataReader
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        /// <param name="parameters">SQL参数</param>
        public static SqlDataReader ExecuteDataReader(string sql, CommandType type, params SqlParameter[] parameters)
        {
            return DataReaderLogic(connection => GetSqlCommand(connection, sql, type, parameters));
        }

        /// <summary>
        /// 返回SqlDataReader
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="parameters">SQL参数</param>
        public static SqlDataReader ExecuteDataReader(string sql, params SqlParameter[] parameters)
        {
            return DataReaderLogic(conn => GetSqlCommand(conn, sql, CommandType.Text, parameters));
        }

        /// <summary>
        /// 返回SqlDataReader
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        public static SqlDataReader ExecuteDataReader(string sql, CommandType type)
        {
            return DataReaderLogic(conn => GetSqlCommand(conn, sql, type, null));
        }

        /// <summary>
        /// 返回SqlDataReader
        /// </summary>
        /// <param name="sql">T-SQL</param>
        public static SqlDataReader ExecuteDataReader(string sql)
        {
            return DataReaderLogic(conn => GetSqlCommand(conn, sql, CommandType.Text, null));
        }

        /// <summary>
        /// 获取指定类型数据,从SqlDataReader转换
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">T-SQL</param>
        public static T ExecuteDataReader<T>(string sql)
        {
            return DataReaderSingleLogic<T>(sql, reader =>
            {
                return TypeConversion.SqlDataReaderToType<T>(reader);
            }, null);
        }

        /// <summary>
        /// 获取指定类型数据,从SqlDataReader转换
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">T-SQL</param>
        /// <param name="parameter">参数</param>
        public static T ExecuteDataReader<T>(string sql, params SqlParameter[] parameters)
        {
            return DataReaderSingleLogic<T>(sql, reader =>
            {
                return TypeConversion.SqlDataReaderToType<T>(reader);
            }, parameters);
        }

        /// <summary>
        /// 获取指定类型数据集合，从SqlDataReader转换
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">T-SQL</param>
        /// <param name="parameters">参数</param>
        public static IEnumerable<T> ExecuteDataReaderList<T>(string sql, params SqlParameter[] parameters)
        {
            return DataReaderListLogic<T>(sql, reader =>
             {
                 return TypeConversion.SqlDataReaderToTypeList<T>(reader);
             }, parameters);
        }

        /// <summary>
        /// DataReader数据集合获取统用逻辑
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">T-SQL</param>
        /// <param name="func">类型转换方法</param>
        /// <param name="parameters">参数</param>
        private static IEnumerable<T> DataReaderListLogic<T>(string sql, Func<SqlDataReader, IEnumerable<T>> func, params SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(Stringbuilder.ConnectionString);
            SqlCommand command = null;
            try
            {
                connection.Open();
                command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return func.Invoke(reader);
            }
            catch (Exception)
            {
                connection.Close();
                throw;
            }
            finally
            {
                command?.Parameters.Clear();
            }
        }
        /// <summary>
        /// DataReader数据单一对象获取统用逻辑
        /// </summary>
        /// <typeparam name="T">数据类型</typeparam>
        /// <param name="sql">T-SQL</param>
        /// <param name="func">类型转换方法</param>
        /// <param name="parameters">参数</param>
        private static T DataReaderSingleLogic<T>(string sql, Func<SqlDataReader, T> func, params SqlParameter[] parameters)
        {
            SqlConnection connection = new SqlConnection(Stringbuilder.ConnectionString);
            SqlCommand command = null;
            try
            {
                connection.Open();
                command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
                return func.Invoke(reader);
            }
            catch (Exception)
            {
                connection.Close();
                throw;
            }
            finally
            {
                command?.Parameters.Clear();
            }
        }

        /// <summary>
        /// DataReader数据对象获取通用逻辑
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="func">类型转换方法</param>
        /// <param name="parameters">参数</param>
        private static SqlDataReader DataReaderLogic(Func<SqlConnection, SqlCommand> func)
        {
            SqlConnection connection = new SqlConnection(Stringbuilder.ConnectionString);
            SqlCommand command = null;

            try
            {
                connection.Open();
                command = func.Invoke(connection);
                return command.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception)
            {
                connection.Close();
                throw;
            }
            finally
            {
                command?.Parameters.Clear();
            }
        }

        #endregion

        #region SqlDataTable
        /// <summary>
        /// 返回DataTable,单表查询
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        /// <param name="parameters">SQL参数</param>
        public static DataTable ExecuteDataTable(string sql, CommandType type, params SqlParameter[] parameters)
        {
            return UsingSqlConnection(connection =>
            {
                DataTable table = new DataTable();
                SqlCommand command = GetSqlCommand(connection, sql, type, parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(table);
                return table;
            });
        }

        /// <summary>
        /// 返回DataTable,单表查询
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="parameters">SQL参数</param>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters)
        {
            return UsingSqlConnection(connection =>
            {
                DataTable table = new DataTable();
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(table);
                return table;
            });
        }
        /// <summary>
        /// 返回DataTable,单表查询
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        public static DataTable ExecuteDataTable(string sql, CommandType type)
        {
            return UsingSqlConnection(connection =>
            {
                DataTable table = new DataTable();
                SqlCommand command = GetSqlCommand(connection, sql, type, null);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(table);
                return table;
            });
        }
        /// <summary>
        /// 返回DataTable,单表查询
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        /// <param name="parameters">SQL参数</param>
        public static DataTable ExecuteDataTable(string sql)
        {
            return UsingSqlConnection(connection =>
            {
                DataTable table = new DataTable();
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, null);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(table);
                return table;
            });
        }
        #endregion

        #region SqlDataSet

        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        /// <param name="parameters">SQL参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string[] sqls, CommandType type, params SqlParameter[] parameters)
        {
            string sql = sqls.Length > 0 ? string.Join("\r\n", sqls) : "";
            return UsingSqlConnection(connection =>
            {
                DataSet ds = new DataSet();
                SqlCommand command = GetSqlCommand(connection, sql, type, parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
                return ds;
            });
        }
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        /// <param name="parameters">SQL参数</param>
        public static DataSet ExecuteDataSet(string[] sqls, params SqlParameter[] parameters)
        {
            string sql = sqls.Length > 0 ? string.Join("\r\n", sqls) : "";
            return UsingSqlConnection(connection =>
            {
                DataSet ds = new DataSet();
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
                return ds;
            });
        }
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        public static DataSet ExecuteDataSet(string[] sqls, CommandType type)
        {
            string sql = sqls.Length > 0 ? string.Join("\r\n", sqls) : "";
            return UsingSqlConnection(connection =>
            {
                DataSet ds = new DataSet();
                SqlCommand command = GetSqlCommand(connection, sql, type, null);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
                return ds;
            });
        }
        /// <summary>
        /// 返回DataSet
        /// </summary>
        /// <param name="sql">T-SQL</param>
        public static DataSet ExecuteDataSet(string[] sqls)
        {
            string sql = sqls.Length > 0 ? string.Join("\r\n", sqls) : "";
            return UsingSqlConnection(connection =>
            {
                DataSet ds = new DataSet();
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, null);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(ds);
                return ds;
            });
        }


        #endregion

        //这里是写的一些测试代码，不要在正式中使用没经过测试
        #region SqlDataAdapter
        public static DataTable SqlDataAdapterInsert(string sql, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            return UsingSqlConnection(connection =>
            {

                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, null);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                //两种插入的区别就是一个是使用sql+parame，创建空DataRow的时候使用参数化的Sql来执行插入
                //一个是把参数具体化写入DataRow，在DataRow中创建好对象，执行sql的时候直接从DataRow从获取所需参数

                //这里必须要创建插入的sql，自己写或者使用SqlCommandBuilder都行
                {
                    SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(adapter);
                    adapter.InsertCommand = sqlBuilder.GetInsertCommand();
                    adapter.Fill(dt);
                    DataRow row = dt.NewRow();
                    row["Content"] = "123";
                    row["CreateTime"] = "2018-01-01";
                    dt.Rows.Add(row);
                    adapter.Update(dt);
                }
                //使用sql插入数据
                {
                    //SqlCommand InsertCommand = new SqlCommand("insert into Message values(@Content,@CreateTime)", connection);
                    //InsertCommand.Parameters.AddRange(parameters);
                    //adapter.InsertCommand = InsertCommand;
                    //adapter.Fill(dt);
                    //dt.Rows.Add(dt.NewRow());
                    //adapter.Update(dt);
                }
                return dt;
            });
        }


        public static DataTable SqlDataAdapterBuilder(string sql, Action<DataTable, SqlCommandBuilder> action, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            return UsingSqlConnection(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);
                SqlCommandBuilder sqlBuilder = new SqlCommandBuilder(adapter);
                action.Invoke(dt, sqlBuilder);
                return dt;
            });
        }
        public static DataTable SqlDataAdapterTSql(string sql, Action<DataTable, SqlDataAdapter> action, params SqlParameter[] parameters)
        {
            DataTable dt = new DataTable();
            return UsingSqlConnection(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                SqlDataAdapter adapter = new SqlDataAdapter(command);
                adapter.Fill(dt);
                action.Invoke(dt, adapter);
                adapter.Update(dt);
                return dt;
            });
        }

        /// <summary>
        /// 检索有关所有表中包含的架构信息
        /// </summary>
        /// <returns></returns>
        public static DataTable SqlDataSchema()
        {
            return UsingSqlConnection(connection =>
             {
                 return connection.GetSchema("Tables");
             });
        }


        /// <summary>
        /// 批量操作，事务操作
        /// </summary>
        /// <param name="sqls">T-SQL集合</param>
        public static void ExecuteNonQueryTransaction(string[] sqls)
        {
            string sql = sqls.Length > 0 ? string.Join("\r\n", sqls) : string.Empty;
            UsingSqlConnection(connection =>
            {
                SqlTransaction transaction = connection.BeginTransaction();
                try
                {
                    SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, null);
                    command.Transaction = transaction;
                    command.ExecuteNonQuery();
                    transaction.Commit();
                }
                catch
                {
                    transaction.Rollback();
                    throw;
                }
            });
        }
        #endregion

        #region 返回受影响的行数

        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        /// <param name="parameters">SQL参数</param>
        public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] parameters)
        {
            return UsingSqlConnection(connection =>
             {
                 SqlCommand command = GetSqlCommand(connection, sql, type, parameters);
                 return command.ExecuteNonQuery();
             });
        }
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="parameters">SQL参数</param>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters)
        {
            return UsingSqlConnection(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                return command.ExecuteNonQuery();
            });
        }
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        public static int ExecuteNonQuery(string sql, CommandType type)
        {
            return UsingSqlConnection(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, type, null);
                return command.ExecuteNonQuery();
            });
        }
        /// <summary>
        /// 返回受影响的行数
        /// </summary>
        /// <param name="sql">T-SQL</param>
        public static int ExecuteNonQuery(string sql)
        {
            return UsingSqlConnection(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, null);
                return command.ExecuteNonQuery();
            });
        }

        #endregion

        #region 返回单行单列
        /// <summary>
        /// 返回单行单列
        /// </summary>
        public static object ExecuteScalar(string sql)
        {
            return UsingSqlConnection<object>(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, null);
                return command.ExecuteScalar();
            });
        }
        /// <summary>
        /// 返回单行单列
        /// </summary>
        public static object ExecuteScalar(string sql, CommandType type, params SqlParameter[] parameters)
        {
            return UsingSqlConnection<object>(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, type, parameters);
                return command.ExecuteScalar();
            });
        }
        /// <summary>
        /// 返回单行单列
        /// </summary>
        public static object ExecuteScalar(string sql, params SqlParameter[] parameters)
        {
            return UsingSqlConnection<object>(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, CommandType.Text, parameters);
                return command.ExecuteScalar();
            });
        }
        /// <summary>
        /// 返回单行单列
        /// </summary>
        public static object ExecuteScalar(string sql, CommandType type)
        {
            return UsingSqlConnection<object>(connection =>
            {
                SqlCommand command = GetSqlCommand(connection, sql, type, null);
                return command.ExecuteScalar();
            });
        }
        #endregion

        #region 私有函数
        /// <summary>
        /// 开启SqlConnection作用域
        /// </summary>
        private static T UsingSqlConnection<T>(Func<SqlConnection, T> func)
        {
            using (SqlConnection connection = new SqlConnection(Stringbuilder.ConnectionString))
            {
                try
                {
                    connection.Open();
                    T t = func.Invoke(connection);
                    connection.Close();
                    return t;
                }
                catch
                {
                    connection.Close();
                    throw;
                }
            }
        }
        /// <summary>
        /// 开启SqlConnection作用域
        /// </summary>
        private static void UsingSqlConnection(Action<SqlConnection> action)
        {
            using (SqlConnection connection = new SqlConnection(Stringbuilder.ConnectionString))
            {
                try
                {
                    connection.Open();
                    action.Invoke(connection);
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        /// <summary>
        /// SqlCommand对象构造
        /// </summary>
        /// <param name="connection">数据库连接</param>
        /// <param name="sql">T-SQL</param>
        /// <param name="type">SQL指令类型</param>
        /// <param name="parameters">SQL参数</param>
        private static SqlCommand GetSqlCommand(SqlConnection connection, string sql, CommandType type, params SqlParameter[] parameters)
        {
            if (null == connection)
                throw new DataException("SqlConnection空指针");

            if (connection.State != ConnectionState.Open)
                throw new DataException("SqlConnection连接未打开");

            SqlCommand comm = new SqlCommand(sql, connection);
            comm.CommandType = type;
            if (parameters != null)
                comm.Parameters.AddRange(parameters);

            return comm;
        }

        #endregion
    }
}